package com.sunyard.pcc.column;

import com.sunyard.pcc.CodeResourceUtil;
import org.apache.commons.lang.StringUtils;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class CreateBean {

    private Connection connection;
    private  String url;
    private  String username;
    private  String password;
    private  String rt = "\r\t";
    private String SQLTables = "show tables";
    private String method;
    private String argv;
    private static String selectStr;
    private static String from;

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        selectStr = "select ";
        from = " from ";
    }

    public void setMysqlInfo(String url, String userName, String password) {
        this.url = url;
        this.username = userName;
        this.password = password;
    }

    public Connection getConnection() throws SQLException{
        return DriverManager.getConnection(url, username, password);
    }

    public List<ColumnData> getColumnDatas(String tableName)
            throws SQLException
    {
        String SQLColumns = "select column_name ,data_type,column_comment,0,0,character_maximum_length,is_nullable nullable from information_schema.columns where table_name =  '" + tableName + "' " + "and table_schema =  '" + CodeResourceUtil.databaseName + "'";

        Connection con = getConnection();
        PreparedStatement ps = con.prepareStatement(SQLColumns);
        List columnList = new ArrayList();
        ResultSet rs = ps.executeQuery();
        StringBuffer str = new StringBuffer();
        StringBuffer getset = new StringBuffer();
        int length;
        while (rs.next()) {
            String columeName = rs.getString(1).toLowerCase();
            String memberName = "";
            String[] strings = columeName.split("_");
            if ((length = strings.length) != 0) {
                for (int i = 0; i < length; i++) {
                    if (i != 0)
                        memberName = memberName.concat(strings[i].substring(0, 1).toUpperCase() + strings[i].substring(1, strings[i].length()));
                    else
                        memberName = memberName.concat(strings[i]);
                }
            }
            String type = rs.getString(2);
            String comment = rs.getString(3);
            String precision = rs.getString(4);
            String scale = rs.getString(5);
            String charmaxLength = rs.getString(6) == null ? "" : rs.getString(6);
            String nullable = TableConvert.getNullAble(rs.getString(7));
            type = getType(type, precision, scale);

            ColumnData cd = new ColumnData();
            cd.setColumnName(columeName);
            cd.setMemberName(memberName);
            cd.setDataType(type);
            cd.setColumnType(rs.getString(2));
            cd.setColumnComment(comment);
            cd.setPercision(precision);
            cd.setScale(scale);
            cd.setCharmaxLength(charmaxLength);
            cd.setNullAble(nullable);
            formatFieldClassType(cd);
            columnList.add(cd);
        }
        this.argv = str.toString();
        this.method = getset.toString();
        rs.close();
        ps.close();
        con.close();
        return columnList;
    }

    /**
     * 生成类域
     * @param tableName 表名字
     * @return 类域字符串
     * @throws SQLException sql异常
     */
    public String getBeanFeilds(String tableName) throws SQLException {
        List<ColumnData> dataList = getColumnDatas(tableName);
        StringBuffer str = new StringBuffer();
        String name, type, comment;

        for (ColumnData d : dataList) {

            name = d.getMemberName();
            type = d.getDataType();
            comment = d.getColumnComment();

            str.append("\r\t").append("private ").append(type + " ").append(name).append(";//   ").append(comment);
        }
        this.argv = str.toString();
        return this.argv;
    }

    private void formatFieldClassType(ColumnData columnt) {
        String fieldType = columnt.getColumnType();
        String scale = columnt.getScale();

        if ("N".equals(columnt.getNullAble())) {
            columnt.setOptionType("required:true");
        }
        if (("datetime".equals(fieldType)) || ("time".equals(fieldType))) {
            columnt.setClassType("easyui-datetimebox");
        } else if ("date".equals(fieldType)) {
            columnt.setClassType("easyui-datebox");
        } else if ("int".equals(fieldType)) {
            columnt.setClassType("easyui-numberbox");
        } else if ("number".equals(fieldType)) {
            if ((StringUtils.isNotBlank(scale)) && (Integer.parseInt(scale) > 0)) {
                columnt.setClassType("easyui-numberbox");
                if (StringUtils.isNotBlank(columnt.getOptionType()))
                    columnt.setOptionType(columnt.getOptionType() + "," + "precision:2,groupSeparator:','");
                else
                    columnt.setOptionType("precision:2,groupSeparator:','");
            }
            else {
                columnt.setClassType("easyui-numberbox");
            }
        } else if (("float".equals(fieldType)) || ("double".equals(fieldType)) || ("decimal".equals(fieldType))) {
            columnt.setClassType("easyui-numberbox");
            if (StringUtils.isNotBlank(columnt.getOptionType()))
                columnt.setOptionType(columnt.getOptionType() + "," + "precision:2,groupSeparator:','");
            else
                columnt.setOptionType("precision:2,groupSeparator:','");
        }
        else {
            columnt.setClassType("easyui-validatebox");
        }
    }

    public String getType(String dataType, String precision, String scale) {
        dataType = dataType.toLowerCase();
        if (dataType.contains("char"))
            dataType = "java.lang.String";
        else if (dataType.contains("int"))
            dataType = "java.lang.Integer";
        else if (dataType.contains("float"))
            dataType = "java.lang.Float";
        else if (dataType.contains("double"))
            dataType = "java.lang.Double";
        else if (dataType.contains("number")) {
            if ((StringUtils.isNotBlank(scale)) && (Integer.parseInt(scale) > 0))
                dataType = "java.math.BigDecimal";
            else if ((StringUtils.isNotBlank(precision)) && (Integer.parseInt(precision) > 6))
                dataType = "java.lang.Long";
            else
                dataType = "java.lang.Integer";
        }
        else if (dataType.contains("decimal"))
            dataType = "BigDecimal";
        else if (dataType.contains("date"))
            dataType = "java.time.LocalDateTime";
        else if (dataType.contains("time"))
            dataType = "java.sql.Timestamp";
        else if (dataType.contains("clob"))
            dataType = "java.sql.Clob";
        else {
            dataType = "java.lang.Object";
        }
        return dataType;
    }

    public String getTablesNameToClassName(String tableName) {
        String[] split = tableName.split("_");
        if (split.length > 1) {
            StringBuffer sb = new StringBuffer();
            for (int i = 0; i < split.length; i++) {
                String tempTableName = split[i].substring(0, 1).toUpperCase() + split[i].substring(1, split[i].length());
                sb.append(tempTableName);
            }

            return sb.toString();
        }
        String tempTables = split[0].substring(0, 1).toUpperCase() + split[0].substring(1, split[0].length());
        return tempTables;
    }

    public void createFile(String path, String fileName, String str) throws IOException {
        FileWriter writer = new FileWriter(new File(path + fileName));
        writer.write(new String(str.getBytes("utf-8")));
        writer.flush();
        writer.close();
    }

    public Map<String, Object> getAutoCreateSql(String tableName) throws Exception {
        Map sqlMap = new HashMap();
        List columnDatas = getColumnDatas(tableName);
        String columns = getColumnSplit(columnDatas);
        String members = getMemberSplit(columnDatas);
        String[] columnList = getColumnList(columns);
        String columnFields = getColumnFields(columns);
        String insert = "insert into " + tableName + "(" + columns.replaceAll("\\|", ",") + ")\n values(#{" + members.replaceAll("\\|", "},#{") + "})";
        String update = getUpdateSql(tableName, columnDatas);
        String updateSelective = getUpdateSelectiveSql(tableName, columnDatas);
        String selectById = getSelectByIdSql(tableName, columnList);
        String delete = getDeleteSql(tableName, columnList);
        sqlMap.put("columnList", columnList);
        sqlMap.put("columnFields", columnFields);
        sqlMap.put("insert", insert.replace("#{createTime}", "now()").replace("#{updateTime}", "now()"));
        sqlMap.put("update", update.replace("#{createTime}", "now()").replace("#{updateTime}", "now()"));
        sqlMap.put("delete", delete);
        sqlMap.put("updateSelective", updateSelective);
        sqlMap.put("selectById", selectById);
        return sqlMap;
    }

    public String getDeleteSql(String tableName, String[] columnsList) {
        StringBuffer sb = new StringBuffer();
        sb.append("delete ");
        sb.append("\t from ").append(tableName).append(" where ");
        sb.append(columnsList[0]).append(" = #{").append(columnsList[0]).append("}");
        return sb.toString();
    }

    public String getSelectByIdSql(String tableName, String[] columnsList)  {
        StringBuffer sb = new StringBuffer();
        sb.append("select <include refid=\"Base_Column_List\" /> \n");
        sb.append("\t from ").append(tableName).append(" where ");
        sb.append(columnsList[0]).append(" = #{").append(columnsList[0]).append("}");
        return sb.toString();
    }

    public String getColumnFields(String columns) {
        String fields = columns;
        if ((fields != null) && (!"".equals(fields))) {
            fields = fields.replaceAll("[|]", ",");
        }
        return fields;
    }

    public String[] getColumnList(String columns) {
        String[] columnList = columns.split("[|]");
        return columnList;
    }

    public String getUpdateSql(String tableName, List<ColumnData> columnsList) {
        StringBuffer sb = new StringBuffer();

        for (int i = 1; i < columnsList.size(); i++) {
            ColumnData columnData = columnsList.get(i);
            String column = columnData.getColumnName();
            if ("CREATETIME".equals(column.toUpperCase())) {
                continue;
            }
            if ("UPDATETIME".equals(column.toUpperCase()))
                sb.append(column + "=now()");
            else {
                sb.append(column + "=#{" + columnData.getMemberName() + "}");
            }
            if (i + 1 < columnsList.size()) {
                sb.append(",");
            }
        }
        String update = "update " + tableName + " set " + sb.toString() + " where " + columnsList.get(0).getColumnName() + "=#{" +columnsList.get(0).getColumnName() + "}";
        return update;
    }


    public String getUpdateSelectiveSql(String tableName, List<ColumnData> columnList) {
        StringBuffer sb = new StringBuffer();
        ColumnData cd = (ColumnData)columnList.get(0);
        sb.append("\t<trim  suffixOverrides=\",\" >\n");
        for (int i = 1; i < columnList.size(); i++) {
            ColumnData data = (ColumnData)columnList.get(i);
            String columnName = data.getColumnName();
            String memberName = data.getMemberName();
            sb.append("\t<if test=\"").append(memberName).append(" != null ");

            if ("String" == data.getDataType()) {
                sb.append(" and ").append(memberName).append(" != ''");
            }
            sb.append(" \">\n\t\t");
            sb.append(columnName + "=#{" + memberName + "},\n");
            sb.append("\t</if>\n");
        }
        sb.append("\t</trim>");
        String update = "update " + tableName + " set \n" + sb.toString() + " where " + cd.getColumnName() + "=#{" + cd.getMemberName() + "}";
        return update;
    }

    public String getColumnSplit(List<ColumnData> columnList) {
        StringBuffer commonColumns = new StringBuffer();
        for (ColumnData data : columnList) {
            commonColumns.append(data.getColumnName() + "|");
        }
        return commonColumns.delete(commonColumns.length() - 1, commonColumns.length()).toString();
    }

    public String getMemberSplit(List<ColumnData> columnList) {
        StringBuffer memberNameColumns = new StringBuffer();
        for (ColumnData data : columnList) {
            memberNameColumns.append(data.getMemberName() + "|");
        }
        return memberNameColumns.delete(memberNameColumns.length() - 1, memberNameColumns.length()).toString();
    }
}
